package main

import (
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/go-xorm/xorm"
	"time"
)

type TPlatUser struct {
	CreateId     string    `xorm:"comment('创建人') VARCHAR(32)"`
	CreateTime   time.Time `xorm:"comment('创建时间') DATETIME"`
	DelFlag      int       `xorm:"not null default 0 comment('是否删除 0 未删除 ,1 已删除 ') TINYINT(1)"`
	EnableFlag   int       `xorm:"not null default 0 comment('是否可用 0 可用 ,1 不可用 ') TINYINT(1)"`
	IconUrl      string    `xorm:"not null default '0' comment('第三方用户头像') VARCHAR(255)"`
	Id           int       `xorm:"not null pk autoincr comment('主键ID') INT"`
	IdentityCode string    `xorm:"default '' comment('身份编码') VARCHAR(64)"`
	NickName     string    `xorm:"default '' comment('昵称') VARCHAR(64)"`
	ParentId     string    `xorm:"default '' comment('第三方用户ID上级ID') index(IDX_PLAT_USER_4) VARCHAR(32)"`
	ParentIds    string    `xorm:"default '' comment('第三方用户所有上级ID') VARCHAR(255)"`
	PlatId       int       `xorm:"not null comment('平台ID') index index(IDX_PLAT_USER_2) index(IDX_PLAT_USER_3) index(IDX_PLAT_USER_4) INT"`
	ThirdId      string    `xorm:"not null comment('第三方用户ID,用于做唯一标识') index(IDX_PLAT_USER_3) VARCHAR(64)"`
	UpdateId     string    `xorm:"comment('修改人') VARCHAR(32)"`
	UpdateTime   time.Time `xorm:"comment('修改时间') DATETIME"`
	UserId       int       `xorm:"not null comment('授权用户ID') index(IDX_PLAT_USER_2) INT"`
	UserName     string    `xorm:"comment('三方账号') VARCHAR(64)"`
	VipCode      string    `xorm:"default '' comment('vip编码') VARCHAR(64)"`
}

type TUserFriendRemark struct {
	CreateTime   time.Time `xorm:"default 'CURRENT_TIMESTAMP' comment('创建时间') DATETIME"`
	DelFlag      int       `xorm:"not null default 0 comment('是否删除(0 未删除，1已删除)') TINYINT(1)"`
	FriendId     int64     `xorm:"not null comment('上下级好友iD') index(Idx_plat_user_friend) BIGINT"`
	FriendRemark string    `xorm:"comment('好友备注') index(Idx_plat_user_friend) VARCHAR(255)"`
	FriendType   int       `xorm:"not null comment('好友类型(1上级好友， 2下级好友)') index(Idx_plat_user_friend) INT"`
	Id           int       `xorm:"not null pk autoincr comment('主键自增ID') INT"`
	PlatId       int64     `xorm:"not null comment('平台ID') index(Idx_plat_user_friend) BIGINT"`
	UpdateTime   time.Time `xorm:"default 'CURRENT_TIMESTAMP' comment('更新时间') DATETIME"`
	UserId       int64     `xorm:"not null comment('用户ID') index(Idx_plat_user_friend) BIGINT"`
}

func main() {

}

func TestDemo(dB *xorm.Engine) {
	fmt.Println("聊天室测试mysql")

	// 插入数据
	// InsertTestUserData(dB)
	//InsertTestGroupData(dB)

	// SearchTestData(dB)

	// SqlTest(dB)
	//data, err := GetSearchUserInfo(dB, 3193000002)
	//fmt.Println("搜索用户信息为:", data, err)

	// 搜索用户下级好友
	//data, err := GetSearchDnFriends(dB, 190051,1073350, "harber")
	//fmt.Println("搜索下级好友:", data, err)

	// 状态更新测试
	//err := UpdateOrderState(dB, 190051, "2007080746234R6ZDP", 1)
	//fmt.Println("更新结果：", err)

	// 查询下级好友
	// DFriends(dB)

	// 搜索下级好友
	// SearchDFriends(dB)
	// GetOneFriendRemarkInfo(190051,3204000002, 1, 276700005, dB)
	GroupByMember(dB)

}

func GetSearchDnFriends(dB *xorm.Engine, platId, thirdId uint32, nickName string) ([]*TPlatUser, error) {
	friendInfo := make([]*TPlatUser, 0)
	// sql := fmt.Sprintf("SELECT * FROM `chat_customer`.`t_plat_user` WHERE `parent_id` = '1073350' AND `plat_id` = '190051' AND `nick_name` LIKE `%s`", "%"+ nickName, "%")
	//err := dB.SQL(`SELECT * FROM t_plat_user WHERE plat_id = ? AND  parent_id= ? AND nick_name like ?`,
	//	platId, thirdId, "'" + nickName + "'").Find(friendInfo)
	//err := dB.SQL(sql).Find(friendInfo)

	err := dB.Where("plat_id=?", platId).
		And("third_id=?", thirdId).
		And("nick_name like ?", "%"+nickName+"%").
		Find(&friendInfo)
	if err != nil {
		return nil, fmt.Errorf("搜索下级好友信息失败: %s", err)
	}
	return friendInfo, nil
}

func GetSearchUserInfo(dB *xorm.Engine, userId uint32) ([]*TPlatUser, error) {
	friendInfo := make([]*TPlatUser, 0)
	err := dB.Where("user_id=?", userId).
		Find(friendInfo)
	if err != nil {
		return nil, fmt.Errorf("搜索下级好友信息失败: %s", err)
	}
	return friendInfo, nil
}

func SqlTest(dB *xorm.Engine) {
	/*dB.SQL("")
	dB.QueryString("")
	dB.Query("")
	dB.Exec("")*/

	/* SQL 适合查询 */
	// 查询一个
	//data := new(TbGroupInfo)
	//sql := fmt.Sprintf("SELECT * FROM `db_test`.`tb_group_info` WHERE `group_id` = '%d' LIMIT 0,10", 23161218)
	//_, err := dB.SQL(sql).Get(data)
	//if err != nil{
	//	fmt.Println("查询错误：", err)
	//	return
	//}
	//fmt.Println("data:", data)

	//// 查询多个
	//data1 := make([]*TbGroupInfo, 0)
	//err := dB.SQL("SELECT * FROM `db_test`.`tb_group_info` WHERE `plat_id`=?  LIMIT 0,1000",200091 ).Find(&data1)
	//if err != nil{
	//	fmt.Println("查询错误：", err)
	//	return
	//}
	//fmt.Println("data1:", data1)

	/* QueryString 适合查询结果为map, 更新操作 */
	//// 查询一个
	//sql := fmt.Sprintf("SELECT * FROM `db_test`.`tb_group_info` WHERE `group_id` = '%d' LIMIT 0, 10", 23161218)
	//RlsMap, err := dB.QueryString(sql)
	//if err != nil{
	//	fmt.Println("查询错误：", err)
	//	return
	//}
	//fmt.Println("RlsMap:", RlsMap)

	//// 查询多个
	//RlsMap1, err := dB.QueryString("SELECT * FROM `db_test`.`tb_group_info` WHERE `plat_id` = '200091' LIMIT 0,1000" )
	//if err != nil{
	//	fmt.Println("查询错误：", err)
	//	return
	//}
	//fmt.Println("RlsMap1:", RlsMap1)
	//// 更新操作
	//sql := fmt.Sprintf("UPDATE tb_group_info SET `group_remark` = '%s'  WHERE group_id = '%d'", "hshshshshsh" ,26663269)
	//RlsMap, err := dB.QueryString(sql)
	//if err != nil{
	//	fmt.Println("更新错误：", err)
	//	return
	//}
	//fmt.Println("RlsMap:", RlsMap)

	/* Exec 适合插入操作*/
	//// 更新操作
	//sql := fmt.Sprintf("UPDATE tb_group_info SET `group_remark` = '%s'  WHERE group_id = '%d'", "haahahah" ,26663269)
	//_, err := dB.Exec(sql)
	//if err != nil{
	//	fmt.Println("查询错误：", err)
	//	return
	//}

	/* 查询结果为map，value为[]byte类型*/
	//sql := fmt.Sprintf("SELECT * FROM `db_test`.`tb_group_info` WHERE `group_id` = '%d' LIMIT 0, 10", 23161218)
	//RlsMap, err := dB.Query(sql)
	//if err != nil{
	//	fmt.Println("查询错误：", err)
	//	return
	//}
	//fmt.Println("RlsMap:", RlsMap)

	//sql := fmt.Sprintf("SELECT * FROM `db_test`.`tb_group_info` WHERE `group_id` = '%d' LIMIT 0, 10", 23161218)
	//RlsMap, err := dB.QueryInterface(sql)

}

//Live 更新订单状态
func UpdateOrderState(dB *xorm.Engine, platID uint32, orderId string, status int) error {
	sql := fmt.Sprintf("UPDATE chat_customer.t_live_session_extend SET state = %d "+
		"WHERE plat_id=%d AND user_id = %d ", 1, 190051, 3193000006)
	_, err := dB.Exec(sql)
	return err
}

func DFriends(dB *xorm.Engine) {
	sql := fmt.Sprintf(`select count(*) as count from (SELECT
		id,
		plat_id,
		user_id,
		third_id,
		user_name,
		nick_name,
		icon_url,
		parent_id
	FROM
	t_plat_user
	WHERE
	plat_id = '%d' 
	AND parent_id = '%d' 
	AND del_flag = 0
	AND enable_flag = 0
	UNION
	SELECT
	T2.id,
		T2.plat_id,
		T2.user_id,
		T2.third_id,
		T2.user_name,
		T2.nick_name,
		T2.icon_url,
		T2.parent_id
	FROM
	t_plat_user AS T1
	JOIN t_plat_user AS T2 ON T1.third_id = T2.parent_id
	AND T1.plat_id = T2.plat_id
	WHERE
	T1.plat_id = '%d' 
	AND T1.parent_id = '%d' 
	AND ((	T1.del_flag = 0 AND T1.enable_flag = 0 )
	OR ( T2.del_flag = 0 AND T2.enable_flag = 0 )))  AS T3`, 190051, 1072976, 190051, 1072976)
	data := make([]*TPlatUser, 0)
	num, err := dB.SQL(sql).Count(&data)
	if err != nil {
		fmt.Println("查询错误：", err)
	}
	fmt.Println("总数有", num)
}

func SearchDFriends(dB *xorm.Engine) {
	sql := fmt.Sprintf(`select * from (SELECT
		id,
		plat_id,
		user_id,
		third_id,
		user_name,
		nick_name,
		icon_url,
		parent_id
	FROM
	t_plat_user
	WHERE
	plat_id = '%d' 
	AND parent_id = '%d' 
	AND del_flag = 0
	AND enable_flag = 0
	UNION
	SELECT
	T2.id,
		T2.plat_id,
		T2.user_id,
		T2.third_id,
		T2.user_name,
		T2.nick_name,
		T2.icon_url,
		T2.parent_id
	FROM
	t_plat_user AS T1
	JOIN t_plat_user AS T2 ON T1.third_id = T2.parent_id
	AND T1.plat_id = T2.plat_id
	WHERE
	T1.plat_id = '%d' 
	AND T1.parent_id = '%d' 
	AND ((	T1.del_flag = 0 AND T1.enable_flag = 0 )
	OR ( T2.del_flag = 0 AND T2.enable_flag = 0 )))  AS T3
	WHERE
	T3.user_name LIKE '%s'
	OR T3.nick_name LIKE '%s'
	LIMIT 0, 2
    `, 190051, 1072976, 190051, 1072976, "%"+"c"+"%", "%"+"c"+"%")
	data := make([]*TPlatUser, 0)
	err := dB.SQL(sql).Find(&data)
	if err != nil {
		fmt.Println("查询错误：", err)
	}
	for _, v := range data {
		fmt.Println(v)
	}
}

// 新增好友没备注信息
func UpdateFriendRemarkInfo(platID, userID, friendType, friendId uint32, remark string, dB *xorm.Engine) error {
	userFriendRemarkInfo := new(TUserFriendRemark)
	userFriendRemarkInfo.PlatId = int64(platID)
	userFriendRemarkInfo.UserId = int64(userID)
	userFriendRemarkInfo.FriendType = int(friendType)
	userFriendRemarkInfo.FriendId = int64(friendId)
	userFriendRemarkInfo.FriendRemark = remark

	exist, err := dB.Where("plat_id=?", platID).
		And("user_id=?", userID).
		And("friend_type=?", friendType).
		And("friend_id=?", friendId).
		Exist(&TUserFriendRemark{})
	if err != nil {
		return err
	}
	if !exist {
		_, err = dB.InsertOne(userFriendRemarkInfo)
		if err != nil {
			return err
		}
	} else {
		_, err = dB.Where("plat_id=?", platID).
			And("user_id=?", userID).
			And("friend_type=?", friendType).
			And("friend_id=?", friendId).
			Update(&TUserFriendRemark{FriendRemark: remark})
		if err != nil {
			return err
		}
	}
	return nil
}

func GetOneFriendRemarkInfo(platID, userID, friendType, friendId uint32, dB *xorm.Engine) (*TUserFriendRemark, error) {
	userFriendRemarkInfo := new(TUserFriendRemark)
	_, err := dB.Where("plat_id=?", platID).
		Select("plat_id, user_id, friend_id, friend_type, friend_remark").
		And("user_id=?", userID).
		And("friend_type=?", friendType).
		And("friend_id=?", friendId).
		Get(userFriendRemarkInfo)
	if err != nil {
		return nil, err
	}
	fmt.Println(userFriendRemarkInfo)
	return userFriendRemarkInfo, nil
}

type GroupBy struct {
	Count   int `xorm:"not null comment('授权用户ID') INT"`
	GroupId int `xorm:"not null comment('授权用户ID')  INT"`
}

func GroupByMember(dB *xorm.Engine) {
	userFriendRemarkInfo := make([]*GroupBy, 0)
	_, err := dB.Table("t_group_user").
		Select("count(1) count, group_id").
		GroupBy("group_id").
		Where("plat_id=?", 190051).
		Having("count>10").
		Get(&userFriendRemarkInfo)
	if err != nil {
		fmt.Println("分组：", err)
		return
	}
	fmt.Println("分组结果", userFriendRemarkInfo)
	return
}
